package com.uottawa.clickerapp.dao.impl;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import com.uottawa.clickerapp.domain.*;

import java.sql.*;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.List;
import java.util.LinkedList;

/**
 *
 * @author Liam
 */
public class SQLDBHelper {
	
	private static long addedID = 0;

    //Creators
    private static ArrayList<ArrayList<String>> executeDBQuery(String query) {
        Connection conn = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd;
        ArrayList<ArrayList<String>> al = new ArrayList<ArrayList<String>>();

        try {
            String url = "jdbc:mysql://" + DBConst.DBServer + "/" + DBConst.DBName;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, DBConst.DBUser, DBConst.DBPassword);
            
            Statement s = conn.createStatement();

            rs = s.executeQuery(query);
            
            rsmd = rs.getMetaData();
            ArrayList<String> row;
            while(rs.next()){
                
                row = new ArrayList<String>();
                
                for( int i = 1; i <= rsmd.getColumnCount(); i++)
                    row.add(rs.getString(i));
                
                al.add((row));
                row = null;
            }

            rs.close();
            s.close();

        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
            System.err.println(e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    return al;
                } catch (Exception e) { /* ignore close errors */ }
            }
        }
        return null;
    }

    private static boolean executeDBCommand(String command) {

        Connection conn = null;
        boolean complete = true;

        try {
            String url = "jdbc:mysql://" + DBConst.DBServer + "/" + DBConst.DBName;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, DBConst.DBUser, DBConst.DBPassword);
            try {
                Statement s = conn.createStatement();

                s.executeUpdate(command, Statement.RETURN_GENERATED_KEYS);
                
                ResultSet rs = s.getGeneratedKeys();
                if (rs.next()){
                    addedID =rs.getInt(1);
                }

            } catch (MySQLIntegrityConstraintViolationException sqler){  
                System.err.println("Entry already exists");
            }
        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
            System.err.println(e);
            complete = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) { /* ignore close errors */ }
            }
        }
        
        return complete;

    }

    public static boolean store(QuestionSession qs) {
        boolean s = executeDBCommand("INSERT INTO QuestionSession (keyword, password) VALUE('" + qs.getKeyword() + "', '" + qs.getPassword() + "')"); 
        qs.setID(addedID);
        return s;
    }
    
    public static boolean store(Question q) {
        int enabled = 1;
        int multipleSubmit = 1;
        if(!q.isEnabled()) {
        	enabled = 0;
        }
        if(!q.canMultipleSubmit()) {
        	multipleSubmit = 0;
        }
        boolean s = executeDBCommand("INSERT INTO Question (questionSessionID, question, enabled, multiSubmission) VALUE('" + q.getQuestionSessionID() + "', '" + q.getQuestionText() + "', '" + enabled + "', '" + multipleSubmit + "')");
        q.setID(addedID);
        long questionId = addedID;
        for(Answer answer : q.getAnswers()) {
        	answer.setQuestionID(questionId);
        	answer.save();
        }
        return s;
    }
    
    public static boolean store(Answer a) {
        
        boolean s = executeDBCommand("INSERT INTO Answer (questionID, answer) VALUE('" + a.getQuestionID() + "', '" + a.getAnswerText() + "')");
        a.setID(addedID);
        return s;
    }
    
    public static boolean store(long questionID, long answerID, long sessionID, boolean canOverwrite) {
    	
    	if (!submissionExists(questionID, sessionID)){
    		executeDBCommand("INSERT INTO Poll (questionID, answerID, sessionID) VALUE('" + questionID + "', '" + answerID + "', '" + sessionID + "')");
    		return true;
    	} else if (canOverwrite){
    		SQLDBHelper.update(questionID, answerID, sessionID);
    		return true;
    	}
        
        return false;
    }
    
    private static boolean submissionExists(long questionID, long sessionID){
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT * FROM Poll WHERE questionID ='" + questionID + "' AND sessionID = '" + sessionID + "'");
        
        return d.size() > 0;
    }

    public static boolean delete(QuestionSession qs){
        return executeDBCommand("DELETE FROM QuestionSession WHERE ID =" + qs.getID());
    }
    
    public static boolean delete(Question q){
        return executeDBCommand("DELETE FROM Question WHERE ID =" + q.getID());
    }    
    
    public static boolean delete(Answer a){
        return executeDBCommand("DELETE FROM Answer WHERE ID =" + a.getID());
    }
    
    public static boolean deleteAnswers(Long questionID){
        return executeDBCommand("DELETE FROM Answer WHERE questionID =" + questionID);
    }
    
    public static boolean update(QuestionSession qs) {
        
        return executeDBCommand("UPDATE QuestionSession SET keyword ='" + qs.getKeyword() + "', password ='" + qs.getPassword() + "' WHERE ID ='" + qs.getID() + "'");
    }
    
    public static boolean update(Question q) {
        
        return executeDBCommand("UPDATE Question SET question ='" + q.getQuestionText() + "', enabled ='" + q.isEnabled() + "', multiSubmission ='" + q.canMultipleSubmit() + "' WHERE ID ='" + q.getID() + "'");
    }
    
    public static boolean enable(Question q, boolean enable) {
        
        return executeDBCommand("UPDATE Question SET  enabled ='" + (enable == true ? 1 : 0) + "' WHERE ID ='" + q.getID() + "'");
    }
    
    public static boolean update(Answer a) {
        
        return executeDBCommand("UPDATE Answer SET answer ='" + a.getAnswerText() + "' WHERE ID ='" + a.getID() + "'");
    }
    
    public static boolean update(long questionID, long answerID, long sessionID) {
        
        return executeDBCommand("UPDATE Poll SET answerID ='" + answerID + "' WHERE questionID ='" + questionID + "' AND sessionID ='" + sessionID + "'");
    }
    
    public static void fetch(QuestionSession qs){
        
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT keyword, password FROM QuestionSession WHERE ID ='" + qs.getID() + "'");
        
        if (d.size() > 0){
        	qs.setKeyword(d.get(0).get(0));
        	qs.setPassword(d.get(0).get(1));
        	List<Question> questions = SQLDBHelper.getQuestions(qs.getID());
        	qs.setQuestions(questions);
        }

    }
    
    public static void fetch(Question q){
        
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT questionSessionID, question, enabled, multiSubmission FROM Question WHERE ID ='" + q.getID() + "'");
        
        if (d.size() > 0){
        	q.setQuestionSessionID(Long.parseLong(d.get(0).get(0)));
        	q.setQuestionText(d.get(0).get(1));
        	q.setEnabled((Integer.parseInt(d.get(0).get(2)) == 0 ? false : true));
        	q.setMultipleSubmit((Integer.parseInt(d.get(0).get(3)) == 0 ? false : true));
        	        	
        }

    }
    
    public static void fetch(Answer a){
        
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT questionID, answer FROM Answer WHERE ID ='" + a.getID() + "'");
        
        if (d.size() > 0){
        	a.setQuestionID(Long.parseLong(d.get(0).get(0)));
        	a.setAnswerText(d.get(0).get(1));
        }

    }
    
    public static List<QuestionSession> getQuestionSessions(){
    	
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT ID, keyword, password FROM QuestionSession");
        
        LinkedList<QuestionSession> lqs = new LinkedList<QuestionSession>();
        
        for (ArrayList<String> a: d){
	        QuestionSession an = new QuestionSession();
	        an.setID(Long.parseLong(a.get(0)));
	        an.setKeyword(a.get(1));
	        an.setPassword(a.get(2));
	        lqs.addFirst(an);
        }
        
        return lqs;
    }
    
    public static List<String> getSessionNames(){
    	
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT keyword FROM QuestionSession");
        
        LinkedList<String> lqs = new LinkedList<String>();
        
        for (ArrayList<String> a: d){
	        lqs.addFirst(a.get(0));
        }
        
        return lqs;
    }
    
    public static List<String> getNonEmptySessionNames() {
    	 ArrayList<ArrayList<String>> d = executeDBQuery("SELECT s.keyword FROM QuestionSession s where s.ID in ( select distinct questionSessionID from Question q where q.questionSessionID = s.id)  ");
         
         LinkedList<String> lqs = new LinkedList<String>();
         
         for (ArrayList<String> a: d){
 	        lqs.addFirst(a.get(0));
         }
         
         return lqs;
	}
    
    public static List<QuestionSession> getQuestionSessions(String keyword){
    	
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT ID, keyword, password FROM QuestionSession WHERE keyword ='" + keyword + "'");
        
        LinkedList<QuestionSession> lqs = new LinkedList<QuestionSession>();
        
        for (ArrayList<String> a: d){
	        QuestionSession an = new QuestionSession();
	        an.setID(Long.parseLong(a.get(0)));
	        an.setKeyword(a.get(1));
	        an.setPassword(a.get(2));
	        List<Question> questions = SQLDBHelper.getQuestions(an.getID());
        	an.setQuestions(questions);
	        lqs.addFirst(an);
        }
        
        return lqs;
    }
    
    public static List<Question> getQuestions(long questionSessionID){
    	
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT ID, question, enabled, multiSubmission FROM Question WHERE questionSessionID ='" + questionSessionID + "'");
        
        LinkedList<Question> lq = new LinkedList<Question>();
        
        for (ArrayList<String> a: d){
	        Question q = new Question();
	        q.setQuestionSessionID(questionSessionID);
	        q.setID(Long.parseLong(a.get(0)));
	        q.setQuestionText(a.get(1));
	        boolean enabled = true;
	        if (!a.get(2).equals("1")) {
	        	enabled = false;
	        }
	        
	        boolean multipleSubmission = true;
	        if (!a.get(3).equals("1")) {
	        	multipleSubmission = false;
	        }
	        q.setEnabled(enabled);
	        q.setMultipleSubmit(multipleSubmission);
	        List<Answer> answers = getAnswers(q.getID());
	        q.setAnswers(answers);
	        lq.addFirst(q);
        }
        
        return lq;
    }
    
    public static List<Answer> getAnswers(long questionID){
    	
        ArrayList<ArrayList<String>> d = executeDBQuery("SELECT ID, questionID, answer FROM Answer WHERE questionID ='" + questionID + " ORDER BY ID'");
        
        LinkedList<Answer> lq = new LinkedList<Answer>();
        
        for (ArrayList<String> a: d){
	        Answer an = new Answer();
	        an.setID(Long.parseLong(a.get(0)));
	        an.setQuestionID(Long.parseLong(a.get(1)));
	        an.setAnswerText(a.get(2));
	        lq.addFirst(an);
        }
        
        return lq;
    }
    
    public static long getPolls(long answerID){
    	long polls = 0;
    	ArrayList<ArrayList<String>> d = executeDBQuery("SELECT COUNT(*) FROM Poll WHERE answerID ='" + answerID + "'");
    	polls = Long.parseLong(d.get(0).get(0));
    	return polls;
    }

	
}
